﻿using System;
using System.Data.SqlClient;
using sscore;
using sscore.Utils;

namespace DM4
{
    public partial class DM4
    {
        public MailAccount GetMailAccount(string name)
        {
            return GetMailAccount(-1, name);
        }

        public MailAccount GetMailAccount(int id, string name = "")
        {
            BeginFunction("GetMailAccount");

            MailAccount ma = new MailAccount();

            string sql =
                name == ""
                ? String.Format("select * from {0} where {1} = {2}", Tn.MailAccounts, CnMailAccounts.Id, id)
                : String.Format("select * from {0} where LTRIM(RTRIM(UPPER({1}))) = '{2}'", Tn.MailAccounts, CnMailAccounts.Mail, name.Trim().ToUpper());

            WriteDebugLog(sql);

            SqlCommand sel = new SqlCommand(sql, _conn);
            using (SqlDataReader reader = sel.ExecuteReader())
            {
                if (reader.HasRows)
                    try
                    {
                        reader.Read();
                        ma.Id = SysUtils.ConvertFromDBVal<int>(reader[CnMailAccounts.Id]);
                        ma.Mail = SysUtils.ConvertFromDBVal<string>(reader[CnMailAccounts.Mail]);
                        ma.SendServer = SysUtils.ConvertFromDBVal<string>(reader[CnMailAccounts.SendServer]);
                        ma.SendPort = SysUtils.ConvertFromDBVal<string>(reader[CnMailAccounts.SendPort]);
                        ma.SendUser = SysUtils.ConvertFromDBVal<string>(reader[CnMailAccounts.SendUser]);
                        ma.SendPassword = SysUtils.ConvertFromDBVal<string>(reader[CnMailAccounts.SendPassword]);
                        ma.SendUseSSL = SysUtils.ConvertFromDBVal<string>(reader[CnMailAccounts.SendUseSsl]) == Consts.TRUE;

                        ma.ReceiveServer = SysUtils.ConvertFromDBVal<string>(reader[CnMailAccounts.ReceiveServer]);
                        ma.ReceivePort = SysUtils.ConvertFromDBVal<string>(reader[CnMailAccounts.ReceivePort]);
                        ma.ReceiveUser = SysUtils.ConvertFromDBVal<string>(reader[CnMailAccounts.ReceiveUser]);
                        ma.ReceivePassword = SysUtils.ConvertFromDBVal<string>(reader[CnMailAccounts.ReceivePassword]);
                        ma.ReceiveUseSSL = SysUtils.ConvertFromDBVal<string>(reader[CnMailAccounts.ReceiveUseSsl]) == Consts.TRUE;
                    }
                    catch (Exception ex)
                    {
                        WriteLog(String.Format("Error occurred while GetMailAccount: Id [{0}], name [{1}] {2}", id, name, ex.Message), MessageType.Error);
                    }
            }

            EndFunction("GetMailAccount");
            return ma;
        }

        public XMLInfo GetMailAccountList(string usrSessionId)
        {
            BeginFunction("GetMailAccountList");
            
            OperationResult userIdOpResult = GetUserIdBySession(usrSessionId);
            XMLInfo xi = new XMLInfo(userIdOpResult);

            if (!userIdOpResult)
            {
                WriteLog(userIdOpResult.Message, MessageType.Error);
                return xi;
            }

            const string sql = "select * from " + Tn.MailAccounts;

            WriteDebugLog(sql);

            try
            {
                SqlCommand sel = new SqlCommand(sql, _conn);

                using (SqlDataReader reader = sel.ExecuteReader())
                {

                    try
                    {
                        WriteLog("Get mail accounts list operation complete");
                        xi.Load(reader, "accounts");
                    }

                    catch (Exception ex)
                    {
                        WriteLog(ex.Message, MessageType.Error);
                    }
                }
            }

            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction("GetMailAccountList");
            return xi;
        }

        public OperationResult AddMailAccount(string usrSessionId, string mail, string sendServer, string sendPort, string sendUser, string sendPassword, bool sendUseSsl
            , string receiveServer, string receivePort, string receiveUser, string receivePassword, bool receiveUseSsl)
        {
            BeginFunction("AddMailAccount");

            OperationResult userIdOpResult = GetUserIdBySession(usrSessionId);
            if (!userIdOpResult) return userIdOpResult;

            OperationResult result = new OperationResult(userIdOpResult, DM4Consts.ErrorUnknown);

            WriteLog("Attempt to add mail account...");
            try
            {
                MailAccount ma = GetMailAccount(mail);

                if (String.IsNullOrEmpty(ma.Mail))
                {
                    string sql = String.Format("insert  into {0} ([{1}]" +
                                               ",[{2}],[{3}],[{4}],[{5}],[{6}]" +
                                               ",[{7}],[{8}],[{9}],[{10}],[{11}]) "
                        + " values ('{12}', '{13}', '{14}','{15}', '{16}', '{17}'" +
                                               ", '{18}', '{19}','{20}', '{21}', '{22}')",
                        Tn.MailAccounts, CnMailAccounts.Mail
                        , CnMailAccounts.SendServer, CnMailAccounts.SendPort, CnMailAccounts.SendUser, CnMailAccounts.SendPassword, CnMailAccounts.SendUseSsl
                        , CnMailAccounts.ReceiveServer, CnMailAccounts.ReceivePort, CnMailAccounts.ReceiveUser, CnMailAccounts.ReceivePassword, CnMailAccounts.ReceiveUseSsl
                        , mail
                        , sendServer, sendPort, sendUser, sendPassword, (sendUseSsl ? Consts.TRUE : Consts.FALSE)
                        , receiveServer, receivePort, receiveUser, receivePassword, (receiveUseSsl ? Consts.TRUE : Consts.FALSE));

                    WriteDebugLog(sql);

                    using (SqlCommand sc = new SqlCommand(sql, _conn))
                    {
                        sc.ExecuteNonQuery();
                    }
                    ma = GetMailAccount(mail);

                    result.Result = ma.Id;
                    result.Message = String.Format("Mail account [{0}] added", mail);
                    WriteLog(result.Message);
                }
                else
                {
                    result.Result = DM4Consts.ErrorEntityWithSomeNameAlreadyExists;
                    result.Message = String.Format("Mail account  [{0}] already exists", mail);
                    WriteLog(result.Message, MessageType.Error);
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction("AddMailAccount");
            return result;
        }

        public OperationResult EditMailAccount(string usrSessionId, int accountId, string mail, string sendServer, string sendPort, string sendUser, string sendPassword, bool sendUseSsl
            , string receiveServer, string receivePort, string receiveUser, string receivePassword, bool receiveUseSsl)
        {
            BeginFunction("EditMailAccount");

            OperationResult userIdOpResult = GetUserIdBySession(usrSessionId);
            if (!userIdOpResult) return userIdOpResult;

            OperationResult result = new OperationResult(userIdOpResult, DM4Consts.ErrorUnknown);

            WriteLog(String.Format("Attempt to edit mail account with id [{0}]...", accountId));
            try
            {
                MailAccount check = GetMailAccount(mail);
                if ((!String.IsNullOrEmpty(check.Mail)) && (check.Id != accountId))
                {
                    result.Result = DM4Consts.ErrorEntityWithSomeNameAlreadyExists;
                    result.Message = String.Format("Mail Account [{0}] already exists", mail);
                    WriteLog(result.Message, MessageType.Error);
                }
                else
                {
                    MailAccount ma = GetMailAccount(accountId);

                    if (!String.IsNullOrEmpty(ma.Mail))
                    {
                        string sql = String.Format("update [{0}] "
                            + "set [{1}]='{2}', [{3}] = '{4}', [{5}] = '{6}'"
                            + ",[{7}]='{8}', [{9}] = '{10}', [{11}] = '{12}'"
                            + ",[{13}] = '{14}', [{15}] = '{16}'"
                            + ",[{17}]='{18}', [{19}] = '{20}', [{21}] = '{22}'"
                            + " where [{23}] = {24}"
                            , Tn.MailAccounts, CnMailAccounts.Mail, mail
                            , CnMailAccounts.SendServer, sendServer, CnMailAccounts.SendPort, sendPort
                            , CnMailAccounts.SendUser, sendUser, CnMailAccounts.SendPassword, sendPassword
                            , CnMailAccounts.SendUseSsl, (sendUseSsl ? Consts.TRUE : Consts.FALSE)
                            , CnMailAccounts.ReceiveServer, receiveServer, CnMailAccounts.ReceivePort, receivePort
                            , CnMailAccounts.ReceiveUser, receiveUser, CnMailAccounts.ReceivePassword, receivePassword
                            , CnMailAccounts.ReceiveUseSsl, (receiveUseSsl ? Consts.TRUE : Consts.FALSE)
                            , CnMailAccounts.Id, accountId);

                        WriteDebugLog(sql);
                        using (SqlCommand sc = new SqlCommand(sql, _conn))
                        {
                            sc.ExecuteNonQuery();
                        }

                        ma = GetMailAccount(accountId);

                        result.Result = ma.Id;
                        result.Message = String.Format("Mail account with id [{0}] modified", accountId);
                        WriteLog(result.Message);

                    }
                    else
                    {
                        result.Result = DM4Consts.ErrorUnknownEntity;
                        result.Message = String.Format("Unknown mail account with id [{0}]", accountId);
                        WriteLog(result.Message, MessageType.Error);
                    }
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction("EditMailAccount");
            return result;
        }

        public OperationResult DeleteMailAccount(string usrSessionId, int accountId)
        {
            BeginFunction("DeleteMailAccount");

            OperationResult userIdOpResult = GetUserIdBySession(usrSessionId);
            if (!userIdOpResult) return userIdOpResult;

            OperationResult result = new OperationResult(userIdOpResult, DM4Consts.ErrorUnknown);

            WriteLog(String.Format("Attempt to delete mail account [{0}]...", accountId));
            try
            {
                MailAccount ma = GetMailAccount(accountId);

                if (!String.IsNullOrEmpty(ma.Mail))
                {
                    string sql = String.Format("delete from [{0}] where [{1}] = {2}", Tn.MailAccounts, CnMailAccounts.Id, accountId);

                    WriteDebugLog(sql);
                    using (SqlCommand sc = new SqlCommand(sql, _conn))
                    {
                        sc.ExecuteNonQuery();
                    }

                    result.Result = DM4Consts.Ok;
                    result.Message = String.Format("Mail account with id [{0}] deleted", accountId);

                    WriteLog(result.Message);

                }
                else
                {
                    result.Result = DM4Consts.ErrorUnknownEntity;
                    result.Message = String.Format("Unknown mail account with id [{0}]", accountId);
                    WriteLog(result.Message, MessageType.Error);
                }

            }

            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction("DeleteMailAccount");
            return result;
        }

    }
}
